package vinnichenko.lab3.database;

import vinnichenko.lab3.*;
import vinnichenko.lab3.exception.*;
import java.util.*;
import java.sql.*;

/**Java extension packages*/
import javax.servlet.*;
import javax.servlet.http.*;
import javax.xml.parsers.*;
import javax.xml.transform.*;
import javax.xml.transform.dom.*;
import javax.xml.transform.stream.*;
import javax.naming.*;
import javax.sql.*;

/**Other packages*/
import org.apache.log4j.Logger;
import org.apache.log4j.BasicConfigurator;

/**Data access object class for database.
 * @author Igor Vinnichenko
 * @version 1.0
 */
public class EmployeesDAO{
    /**Connection*/
    private Connection con = null;

    /**Data source*/
    private DataSource source = null;
    
    /**Logger*/
    static Logger logger = Logger.getLogger(EmployeesDAO.class);

    /**Constructor*/
    public EmployeesDAO() throws Exception{
        try{
            /**Trying to connect to database*/
            InitialContext initContext = new InitialContext();
            Context envContext  = (Context)initContext.lookup("java:/comp/env");
            source = ( DataSource )envContext.lookup( "jdbc/EmployeesDB" );

            logger.info("Data access object created successfully.");
        }catch(Exception e){
            logger.error("Data access object creation error: "+StackWriter.getFullStackTrace( e ));
            throw new Exception(e);
        }
    }

    /**Method for closing connection when database is not needed.*/
    public void remove()throws Exception{
        try{
            /**Closing connection.*/
            if(con != null && con.isClosed() != true)
                con.close();
            logger.info("Data access object removed successfully.");
        }catch(SQLException e){
            logger.error("Data access object closing error: "+StackWriter.getFullStackTrace( e ));
            throw new Exception(e);
        }
    }

    /**Get employee information from database.
     * @param id of employee.
     * @return object containing information.
     * @throws EmployeeNotFoundException
     */
    public EmployeeInfo getEmployee(String id)
        throws EmployeeNotFoundException, DatabaseConnectException{

        /**Prepared statement*/
        PreparedStatement empInfoQuery = null;

        /**Results of query.*/
        ResultSet results = null;

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            /**Query string*/
            String employeeInfoStatement = "SELECT e.*, d.dname AS \"deptname\", "+
                "(SELECT m.ename FROM emp m WHERE m.empno=e.mgr) AS \"mgrname\" "+
                "FROM emp e, dept d WHERE( e.deptno=d.deptno AND e.empno=?)";

            /**Prepared statement*/
            empInfoQuery = con.prepareStatement( employeeInfoStatement );

            /**Insert ID parameter into query*/
            empInfoQuery.setString(1, id);

            /**Execute query.*/
            results = empInfoQuery.executeQuery();

            /**Create new employee object which will contain result information.*/
            EmployeeInfo emp = new EmployeeInfo();

            /**Fill object with values*/
            if( results.next() ){
                emp.setEmployeeNo( results.getInt( "empno" ) );
                emp.setManager( results.getInt( "mgr" ) );
                emp.setDepartmentNo( results.getInt( "deptno" ) );
                emp.setJob( results.getString( "job" ) );
                emp.setCommisions( results.getDouble( "comm" ) );
                emp.setHireDate( results.getDate( "hiredate" ) );
                emp.setSalary( results.getDouble( "sal" ) );
                emp.setEmployeeName( results.getString( "ename" ) );
                emp.setManagerName( results.getString( "mgrname" ) );
                emp.setDepartmentName( results.getString( "deptname" ) );

                empInfoQuery.close();

                return emp;
            }else {
                /**If no result found - when close statement and throw exception.*/
                empInfoQuery.close();
                throw new EmployeeNotFoundException("Couldn't find employee: " + id);
            }
        }catch(SQLException e){
            throw new EmployeeNotFoundException("Couldn't find employee: " + id + ". ", e);
        }finally{
            try{
                /**Close ResultSet*/
                if(results != null)
                    results.close();

                /**Close PreparedStatement*/
                if(empInfoQuery != null)
                    empInfoQuery.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed())
                    con.close();
            }catch(SQLException e){
                throw new EmployeeNotFoundException("Couldn't find employee: " + id + ". ", e);
            }
        }
    }

    /**Get list with information about all employees from database.
     * @return list containing information.
     * @throws EmployeesNotFoundException
     */
    public List<EmployeeInfo> getHierarchyList()
        throws EmployeesNotFoundException, DatabaseConnectException{

        /**Query statement*/
        String hierarchyStatement = "SELECT e.*, level, d.dname AS \"deptname\", "+
                "(SELECT m.ename FROM emp m WHERE m.empno=e.mgr) AS \"mgrname\" "+
                "FROM emp e, dept d WHERE( e.deptno=d.deptno ) "+
                "START WITH ( e.mgr IS NULL OR e.mgr=0) "+
                "CONNECT BY PRIOR e.empno = e.mgr";

        /**Prepared statement*/
        PreparedStatement prepStmt = null;

        /**Query results.*/
        ResultSet results = null;

        /**Create new empty list*/
        List<EmployeeInfo> hierarchyList = new ArrayList<EmployeeInfo>();

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            /**Preparing statement*/
            prepStmt = con.prepareStatement( hierarchyStatement );

            /**Execute query*/
            results = prepStmt.executeQuery();

            /**get row data*/
            while ( results.next() ) {
                EmployeeInfo emp = new EmployeeInfo();

                /**Get fields from result and set values to new object*/
                emp.setEmployeeNo( results.getInt( "empno" ) );
                emp.setManager( results.getInt( "mgr" ) );
                emp.setDepartmentNo( results.getInt( "deptno" ) );
                emp.setJob( results.getString( "job" ) );
                emp.setCommisions( results.getDouble( "comm" ) );
                emp.setHireDate( results.getDate( "hiredate" ) );
                emp.setSalary( results.getDouble( "sal" ) );
                emp.setEmployeeName( results.getString( "ename" ) );
                emp.setLevel( results.getInt( "level" ) );

                emp.setManagerName( results.getString( "mgrname" ) );
                emp.setDepartmentName( results.getString( "deptname" ) );
            
                /**add object to list*/
                hierarchyList.add( emp );
            }
        }catch(SQLException e){
            throw new EmployeesNotFoundException( "Cannot get hierarchy list.", e );
        }finally{
            try{
                /**Close ResultSet*/
                if(results != null)
                    results.close();

                /**Close PreparedStatement*/
                if(prepStmt != null)
                    prepStmt.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed())
                    con.close();
            }catch(SQLException e){
                throw new EmployeesNotFoundException( "Cannot get hierarchy list.", e );
            }
        }

        /**Return formed list*/
        return hierarchyList;
    }

    /**Add new record to database.
     * @param ename name of new employee.
     * @param job job of new employee.
     * @param mgr manager's number of new employee.
     * @param deptno department number of new employee.
     * @param sal salary of new employee.
     * @param comm commitee of new employee.
     * @param hiredate hire date of new employee.
     * @return object containing information of added employee.
     * @throws EmployeeNotFoundException
     */
    public EmployeeInfo addNewEmployee(String ename, String job, Integer mgr,
                               Integer deptno, Float sal, Float comm, java.sql.Date hiredate)
                               throws EmployeeNotFoundException, DatabaseConnectException{
        Integer empno = null;

        /**Query statement*/
        PreparedStatement addQuery = null;

        /**ResultSet for inserted values.*/
        ResultSet rs = null;

        /**New added employee.*/
        EmployeeInfo emp = null;

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            /**Names of columns we want to be returned after inserting.*/
            String columns [] = {"empno"};

            String addStatement = "INSERT INTO emp VALUES ("+
                                  "emp_id_seq.NEXTVAL,"+
                                  " ?, ?, ?, ?, ?, ?, ?)";

            /**Prepare the query. ID of employee generates automatically from sequence.*/
            addQuery = con.prepareStatement( addStatement, columns );

            /**And insert parameters into prepared query*/
            addQuery.setString(1, ename);
            addQuery.setString(2, job);
            addQuery.setInt(3, mgr);
            addQuery.setDate(4, hiredate);
            addQuery.setFloat(5, sal);
            addQuery.setFloat(6, comm);
            addQuery.setInt(7, deptno);

            /**Execute query*/
            addQuery.executeUpdate();

            /**Looking for generated keys.*/
            rs = addQuery.getGeneratedKeys();

            /**Check result we have got*/
            if( rs.next() ){
                empno = rs.getInt( 1 );
            }
        }catch(SQLException e){
            throw new EmployeeNotFoundException("Couldn't add new employee: " + empno + ". ", e);
        }finally{
            try{
                /**Close ResultSet*/
                if(rs != null)
                    rs.close();

                /**Close PreparedStatement*/
                if(addQuery != null)
                    addQuery.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed() )
                    con.close();
            }catch(SQLException e){
                throw new EmployeeNotFoundException("Couldn't add new employee: " + empno + ". ", e);
            }
        }

        /**Get employee's information and return it.*/
        emp = getEmployee( String.valueOf( empno ) );

        return emp;
    }

    /**Modify record in database.
     * @param empno number of employee.
     * @param ename name of employee.
     * @param job job of employee.
     * @param mgr manager's number of employee.
     * @param deptno department number of employee.
     * @param sal salary of employee.
     * @param comm commitee of employee.
     * @param hiredate hire date of employee.
     * @return object containing information of added employee.
     * @throws EmployeeNotFoundException
     */
    public EmployeeInfo modifyEmployee(Integer empno, String ename, String job, Integer mgr,
                                       Integer deptno, Float sal, Float comm, java.sql.Date hiredate)
                                       throws EmployeeNotFoundException, DatabaseConnectException{

        /**Query statement*/
        PreparedStatement modificationQuery = null;

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            String modifyStatement = "UPDATE emp SET "+
                                     "ename=?, job=?, mgr=?, hiredate=?, sal=?, comm=?, deptno=? "+
                                     "WHERE empno=?";

            modificationQuery = con.prepareStatement( modifyStatement );

            /**And insert parameters into prepared query*/
            modificationQuery.setString(1, ename);
            modificationQuery.setString(2, job);
            modificationQuery.setInt(3, mgr);
            modificationQuery.setDate(4, hiredate);
            modificationQuery.setFloat(5, sal);
            modificationQuery.setFloat(6, comm);
            modificationQuery.setInt(7, deptno);
            modificationQuery.setInt(8, empno);

            /**Update record*/
            modificationQuery.executeUpdate();

            /**Close prepared statement.*/
            modificationQuery.close();
        }catch(SQLException e){
            throw new EmployeeNotFoundException("Couldn't find employee: " + empno + ". ", e);
        }finally{
            try{
                /**Close PreparedStatement*/
                if(modificationQuery != null)
                    modificationQuery.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed())
                    con.close();
            }catch(SQLException e){
                throw new EmployeeNotFoundException("Couldn't find employee: " + empno + ". ", e);
            }
        }

        /**Get information of employee from database*/
        EmployeeInfo emp = getEmployee( String.valueOf( empno ) );

        return emp;
    }

    /**Delete employee method.
     * @param id of employee.
     * @return true if success.
     * @throws EmployeeNotFoundException
     */
    public boolean deleteEmployee(String id)throws EmployeeNotFoundException, DatabaseConnectException{
        /**Prepared statement*/
        PreparedStatement deleteStmt = null;

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            /**Query string*/
            String deleteEmployeeStatement = "DELETE FROM emp WHERE empno=?";

            /**Prepared statement*/
            deleteStmt = con.prepareStatement( deleteEmployeeStatement );

            /**Insert ID parameter into query*/
            deleteStmt.setString(1, id);

            /**Execute query*/
            Integer recordsUpdated = deleteStmt.executeUpdate();

            return (recordsUpdated > 0);
        }catch(SQLException e){
            throw new EmployeeNotFoundException("Couldn't find employee: " + id + ". ", e);
        }finally{
            try{
                /**Close PreparedStatement*/
                if(deleteStmt != null)
                    deleteStmt.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed())
                    con.close();
            }catch(SQLException e){
                throw new EmployeeNotFoundException("Couldn't find employee: " + id + ". ", e);
            }
        }
    }

    /**Get list with information about all departments from database.
     * @return list containing information.
     * @throws DepartmentsNotFoundException
     */
    public List<DepartmentInfo> getDepartmentsList()
        throws DepartmentsNotFoundException, DatabaseConnectException{

        /**Query statement*/
        String deptsStatement = "SELECT * FROM dept";

        /**Prepared statement*/
        PreparedStatement prepStmt = null;

        /**Query results.*/
        ResultSet results = null;

        /**Create new empty list*/
        List<DepartmentInfo> deptsList = new ArrayList<DepartmentInfo>();

        try{
            /**Get connection to the database.*/
            con = source.getConnection();
        }catch(SQLException e){
            throw new DatabaseConnectException("Cannot connect to database.", e);
        }

        try{
            prepStmt = con.prepareStatement( deptsStatement );

            results = prepStmt.executeQuery();

            /**get row data*/
            while ( results.next() ) {
                DepartmentInfo dept = new DepartmentInfo();

                /**Get fields from result and set values to new object*/
                dept.setDeptNo( results.getInt( "deptno" ) );
                dept.setDeptName( results.getString( "dname" ) );
                dept.setLocation( results.getString( "loc" ) );
            
                /**add object to list*/
                deptsList.add( dept );
            }
        }catch(SQLException e){
            throw new DepartmentsNotFoundException( "Cannot get departments list. ", e );
        }finally{
            try{
                /**Close ResultSet*/
                if(results != null)
                    results.close();

                /**Close PreparedStatement*/
                if(prepStmt != null)
                    prepStmt.close();

                /**Close connection to database*/
                if(con != null && !con.isClosed())
                    con.close();
            }catch(SQLException e){
                throw new DepartmentsNotFoundException( "Cannot get departments list. ", e );
            }
        }

        /**Return formed list*/
        return deptsList;
    }
}